#coding=utf-8
#@Time:2020/4/9 1:59 PM
#@Author:希哥
#@File:data_search_lingtong.py

import sql
import openpyxl
import sys
import json

reload(sys)
sys.setdefaultencoding( "utf-8" )

'''
    存储数据
'''
class Case:
    def __init__(self,attrs):
        '''
        初始化用例
        :param attrs:zip类型——>[{key,value},(key1,value1)......]
        '''
        for i in attrs:
            setattr(self, i[0], i[1])

'''
    读取数据
    写入查询结果
'''
class ReadExcel(object):

    def __init__(self,file_name,sheet_name):
        '''
        :param file_name: 文件名
        :param sheet_name: 表单名
        '''
        self.file_name = file_name
        self.sheet_name = sheet_name
        self.wb = openpyxl.load_workbook(file_name)
        self.ws = self.wb.active
        self.sheet = self.wb[sheet_name]

    def read(self):
        '''
        读取excel文件
        :return:返回测试用例
        用例ID：case_id,
        用例标题：title,
        请求地址：url,
        请求方法：method,
        请求头：headers,
        请求数据：data,
        期望结果：expect,
        实际结果：actual,
        用例结果：result
        '''
        nrows = self.sheet.max_row   #获取最大行数
        list1 = [1,2,3,4,5,6]
        cases = []
        titles = []
        for row in range(1, nrows + 1):
            if row != 1:
                case_data = []
                for column in list1:
                    info = self.sheet.cell(row, column).value
                    case_data.append(info)
                case = list(zip(titles, case_data))
                case_obj = Case(case)
                cases.append(case_obj)
            else:
                for column in list1:
                    title = self.sheet.cell(row, column).value
                    titles.append(title)
                if None in titles:
                    raise ValueError("传入的表头的数据有显示为空")
        return cases

    def write(self,row,column,value):
        '''
        输入行列
        写入实际结果到actual、result列中
        :return:
        '''
        self.ws.cell(row=row,column=column,value=value)
        self.wb.save(self.file_name)


class Find():
    def __init__(self,mobile):
        #最近一次下单时间
        self.max_time_sql = "SELECT DATE_FORMAT( `o`.`create_time`, '%Y%m%d' ) AS `创建时间` FROM orders as o LEFT JOIN `user` as u ON o.user_id = u.id AND o.dt = 0 AND u.dt = 0 WHERE u.mobile = '"+mobile+"' AND o.order_type <> 'member'  AND o.order_type <> 'blind_box' AND o.state = 'finish' ORDER BY o.create_time DESC LIMIT 1;"
        #累计下单次数
        self.count_sql = "SELECT COUNT(*) FROM orders as o LEFT JOIN `user` as u ON o.user_id = u.id AND o.dt = 0 AND u.dt = 0 WHERE u.mobile = '"+mobile+"' AND o.order_type <> 'member'  AND o.order_type <> 'blind_box' AND o.state = 'finish'"
        #首次下单时间
        self.first_time_sql = "SELECT DATE_FORMAT( `o`.`create_time`, '%Y%m%d' ) AS `创建时间` FROM orders as o LEFT JOIN `user` as u ON o.user_id = u.id AND o.dt = 0 AND u.dt = 0 WHERE u.mobile = '"+mobile+"' AND o.order_type <> 'member'  AND o.order_type <> 'blind_box' AND o.state = 'finish' ORDER BY o.create_time ASC LIMIT 1;"
        #累计购买金额
        self.sum_money_sql = "SELECT SUM(`o`.`payment_amount` / 100) FROM orders as o LEFT JOIN `user` as u ON o.user_id = u.id AND o.dt = 0 AND u.dt = 0 WHERE u.mobile = '"+mobile+"' AND o.order_type <> 'member'  AND o.order_type <> 'blind_box' AND o.state = 'finish'"

    def max_time(self):
        time = sql.search(self.max_time_sql,'hupo_online')
        return time

    def buy_count(self):
        count = sql.search(self.count_sql,'hupo_online')
        return count

    def first_time(self):
        time = sql.search(self.first_time_sql, 'hupo_online')
        return time

    def sum_money(self):
        money = sql.search(self.sum_money_sql,'hupo_online')
        return money


if __name__ == '__main__':
    r = ReadExcel(r'/Users/chenqiang/Downloads/data.xlsx','重庆非会员')
    res = r.read()
    for i in res:
        try:
            max_time = Find(str(i.mobile)).max_time()
            r.write(i.id + 1,3,max_time)
            count = Find(str(i.mobile)).buy_count()
            r.write(i.id + 1, 4, count)
            first_time = Find(str(i.mobile)).first_time()
            r.write(i.id + 1, 5, first_time)
            sum_money = Find(str(i.mobile)).sum_money()
            r.write(i.id + 1, 6, sum_money)
            print i.id,max_time,count,first_time,sum_money
        except:
            r.write(i.id + 1, 3, '无购买记录')
            r.write(i.id + 1, 4, '无购买记录')
            r.write(i.id + 1, 5, '无购买记录')
            r.write(i.id + 1, 6, '无购买记录')
            print i.id